package com.zzlzd.drp.domain;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import javax.management.relation.Relation;
import java.util.List;
import java.util.Map;

/**
 * @author ceshi
 * @Title:
 * @Package
 * @Description:
 * @date 2021-3-1919:41
 */
public interface RelationRep extends JpaRepository<RelationDto,Integer> {

    @Query(nativeQuery=true, value=" SELECT * FROM relation WHERE uid = ?1 AND level = 1")
    public List<Map<String, Integer>> findPid(Integer uid);



    /***
     * 查找我的下级
     * @param id
     * @return
     */
    @Query(nativeQuery=true, value=" SELECT * FROM relation WHERE pid = ?1 AND level = 1")
    public List<Map<String, Integer>> findDid(Integer id);

    /***
     * 统计我的下级人数
     * @param id
     * @return
     */
    @Query(nativeQuery=true, value=" SELECT COUNT(id) FROM relation WHERE pid = ?1 AND level = 1")
    public Integer getNum(Integer id);

    /***
     * 查看我的一级用户信息
     * @param id
     * @return
     */
    @Query(nativeQuery=true, value=" SELECT u.id id,u.name name,u.head,u.phone, case u.`level` when 0 then '会员' when 1 then '天使' when 2 then '市代' when 3 then '省代' when 4 then '战略合伙人' else '未知' end  level FROM relation r  LEFT JOIN `user` u on u.id=r.uid WHERE r.pid = ?1 AND r.level = 1 ")
    List<Map<String, Object>> getOneMember(Integer id);

    /***
     * 查看二级人员信息
     * @param id
     * @return
     */
    @Query(nativeQuery=true, value=" SELECT u.id id,u.name name,u.head,  IF(IFNULL(u.phone,'')='','',CONCAT(LEFT(u.phone,3),'****',RIGHT(u.phone,4))) phone  , case u.`level` when 0 then '会员' when 1 then '天使' when 2 then '市代' when 3 then '省代' when 4 then '战略合伙人' else '未知' end  level FROM relation r  LEFT JOIN `user` u on u.id=r.uid WHERE r.pid = ?1 AND r.level = 1 ")
    List<Map<String, Object>> getTwoMember(Integer id);

    /***
     * 查找我的上级人员信息
     * @param id
     * @return
     */
    @Query(nativeQuery=true, value=" SELECT u.id,u.name,u.phone,u.level FROM relation r  LEFT JOIN `user` u on u.id=r.pid WHERE r.uid = ?1 AND r.level = 1")
    public Map<String, Object> findUpById(Integer id);
}
